
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/18/2010 02:47:53
-- Generated from EDMX file: D:\Work\PhotoAlbum\PhotoAlbum.Web\PhotoAlbumModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SQL2008_558737_main];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PictureTag_Picture]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PictureTag] DROP CONSTRAINT [FK_PictureTag_Picture];
GO
IF OBJECT_ID(N'[dbo].[FK_PictureTag_Tag]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PictureTag] DROP CONSTRAINT [FK_PictureTag_Tag];
GO
IF OBJECT_ID(N'[dbo].[FK_AlbumInvitedUsers_Album]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AlbumInvitedUsers] DROP CONSTRAINT [FK_AlbumInvitedUsers_Album];
GO
IF OBJECT_ID(N'[dbo].[FK_AlbumInvitedUsers_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AlbumInvitedUsers] DROP CONSTRAINT [FK_AlbumInvitedUsers_User];
GO
IF OBJECT_ID(N'[dbo].[FK_FavoritePictures_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FavoritePictures] DROP CONSTRAINT [FK_FavoritePictures_User];
GO
IF OBJECT_ID(N'[dbo].[FK_FavoritePictures_Picture]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FavoritePictures] DROP CONSTRAINT [FK_FavoritePictures_Picture];
GO
IF OBJECT_ID(N'[dbo].[FK_UserFriends_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserFriends] DROP CONSTRAINT [FK_UserFriends_User];
GO
IF OBJECT_ID(N'[dbo].[FK_UserFriends_User1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserFriends] DROP CONSTRAINT [FK_UserFriends_User1];
GO
IF OBJECT_ID(N'[dbo].[FK_InvitationAlbum]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Invitations] DROP CONSTRAINT [FK_InvitationAlbum];
GO
IF OBJECT_ID(N'[dbo].[FK_AlbumPicture]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Pictures] DROP CONSTRAINT [FK_AlbumPicture];
GO
IF OBJECT_ID(N'[dbo].[FK_AlbumOwner]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Albums] DROP CONSTRAINT [FK_AlbumOwner];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTagUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserTags] DROP CONSTRAINT [FK_UserTagUser];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTagPicture]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserTags] DROP CONSTRAINT [FK_UserTagPicture];
GO
IF OBJECT_ID(N'[dbo].[FK_PictureComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_PictureComment];
GO
IF OBJECT_ID(N'[dbo].[FK_CommentAuthors]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [FK_CommentAuthors];
GO
IF OBJECT_ID(N'[dbo].[FK_FriendRequestTarget]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FriendRequests] DROP CONSTRAINT [FK_FriendRequestTarget];
GO
IF OBJECT_ID(N'[dbo].[FK_FriendRequestInitiator]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FriendRequests] DROP CONSTRAINT [FK_FriendRequestInitiator];
GO
IF OBJECT_ID(N'[dbo].[FK_UserInvitation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Invitations] DROP CONSTRAINT [FK_UserInvitation];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTagPhotoAlbumUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserTags] DROP CONSTRAINT [FK_UserTagPhotoAlbumUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Pictures]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Pictures];
GO
IF OBJECT_ID(N'[dbo].[Albums]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Albums];
GO
IF OBJECT_ID(N'[dbo].[Comments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comments];
GO
IF OBJECT_ID(N'[dbo].[Invitations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Invitations];
GO
IF OBJECT_ID(N'[dbo].[Tags]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tags];
GO
IF OBJECT_ID(N'[dbo].[PhotoAlbumUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PhotoAlbumUsers];
GO
IF OBJECT_ID(N'[dbo].[UserTags]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserTags];
GO
IF OBJECT_ID(N'[dbo].[FriendRequests]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FriendRequests];
GO
IF OBJECT_ID(N'[dbo].[PictureTag]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PictureTag];
GO
IF OBJECT_ID(N'[dbo].[AlbumInvitedUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AlbumInvitedUsers];
GO
IF OBJECT_ID(N'[dbo].[FavoritePictures]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FavoritePictures];
GO
IF OBJECT_ID(N'[dbo].[UserFriends]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserFriends];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Pictures'
CREATE TABLE [dbo].[Pictures] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [Title] nvarchar(max)  NULL,
    [Image] nvarchar(max)  NOT NULL,
    [Thumbnail] nvarchar(max)  NULL,
    [AlbumId] int  NOT NULL,
    [ContentType] varchar(30)  NULL
);
GO

-- Creating table 'Albums'
CREATE TABLE [dbo].[Albums] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [DateCreated] datetime  NOT NULL,
    [ViewsCount] int  NOT NULL,
    [VisibilityStore] int  NOT NULL,
    [CommentsPolicyStore] tinyint  NOT NULL,
    [OwnerId] int  NOT NULL
);
GO

-- Creating table 'Comments'
CREATE TABLE [dbo].[Comments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [NeedsApproval] bit  NOT NULL,
    [PictureId] int  NOT NULL,
    [AuthorId] int  NULL
);
GO

-- Creating table 'Invitations'
CREATE TABLE [dbo].[Invitations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [AlbumId] int  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Tags'
CREATE TABLE [dbo].[Tags] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Color] nvarchar(max)  NULL
);
GO

-- Creating table 'PhotoAlbumUsers'
CREATE TABLE [dbo].[PhotoAlbumUsers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(256)  NOT NULL,
    [UserTagId] int  NOT NULL
);
GO

-- Creating table 'UserTags'
CREATE TABLE [dbo].[UserTags] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Position_X] float  NOT NULL,
    [Position_Y] float  NOT NULL,
    [UserId] int  NOT NULL,
    [PictureId] int  NOT NULL,
    [AuthorId] int  NOT NULL
);
GO

-- Creating table 'FriendRequests'
CREATE TABLE [dbo].[FriendRequests] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [TargetId] int  NOT NULL,
    [InitiatorId] int  NOT NULL
);
GO

-- Creating table 'PictureTag'
CREATE TABLE [dbo].[PictureTag] (
    [Pictures_Id] int  NOT NULL,
    [Tags_Id] int  NOT NULL
);
GO

-- Creating table 'AlbumInvitedUsers'
CREATE TABLE [dbo].[AlbumInvitedUsers] (
    [PrivateAlbums_Id] int  NOT NULL,
    [InvitedUsers_Id] int  NOT NULL
);
GO

-- Creating table 'FavoritePictures'
CREATE TABLE [dbo].[FavoritePictures] (
    [FavoriteForUsers_Id] int  NOT NULL,
    [FavoritePictures_Id] int  NOT NULL
);
GO

-- Creating table 'UserFriends'
CREATE TABLE [dbo].[UserFriends] (
    [UserFriends_User1_Id] int  NOT NULL,
    [Friends_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Pictures'
ALTER TABLE [dbo].[Pictures]
ADD CONSTRAINT [PK_Pictures]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Albums'
ALTER TABLE [dbo].[Albums]
ADD CONSTRAINT [PK_Albums]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [PK_Comments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Invitations'
ALTER TABLE [dbo].[Invitations]
ADD CONSTRAINT [PK_Invitations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tags'
ALTER TABLE [dbo].[Tags]
ADD CONSTRAINT [PK_Tags]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PhotoAlbumUsers'
ALTER TABLE [dbo].[PhotoAlbumUsers]
ADD CONSTRAINT [PK_PhotoAlbumUsers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserTags'
ALTER TABLE [dbo].[UserTags]
ADD CONSTRAINT [PK_UserTags]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FriendRequests'
ALTER TABLE [dbo].[FriendRequests]
ADD CONSTRAINT [PK_FriendRequests]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Pictures_Id], [Tags_Id] in table 'PictureTag'
ALTER TABLE [dbo].[PictureTag]
ADD CONSTRAINT [PK_PictureTag]
    PRIMARY KEY NONCLUSTERED ([Pictures_Id], [Tags_Id] ASC);
GO

-- Creating primary key on [PrivateAlbums_Id], [InvitedUsers_Id] in table 'AlbumInvitedUsers'
ALTER TABLE [dbo].[AlbumInvitedUsers]
ADD CONSTRAINT [PK_AlbumInvitedUsers]
    PRIMARY KEY NONCLUSTERED ([PrivateAlbums_Id], [InvitedUsers_Id] ASC);
GO

-- Creating primary key on [FavoriteForUsers_Id], [FavoritePictures_Id] in table 'FavoritePictures'
ALTER TABLE [dbo].[FavoritePictures]
ADD CONSTRAINT [PK_FavoritePictures]
    PRIMARY KEY NONCLUSTERED ([FavoriteForUsers_Id], [FavoritePictures_Id] ASC);
GO

-- Creating primary key on [UserFriends_User1_Id], [Friends_Id] in table 'UserFriends'
ALTER TABLE [dbo].[UserFriends]
ADD CONSTRAINT [PK_UserFriends]
    PRIMARY KEY NONCLUSTERED ([UserFriends_User1_Id], [Friends_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Pictures_Id] in table 'PictureTag'
ALTER TABLE [dbo].[PictureTag]
ADD CONSTRAINT [FK_PictureTag_Picture]
    FOREIGN KEY ([Pictures_Id])
    REFERENCES [dbo].[Pictures]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Tags_Id] in table 'PictureTag'
ALTER TABLE [dbo].[PictureTag]
ADD CONSTRAINT [FK_PictureTag_Tag]
    FOREIGN KEY ([Tags_Id])
    REFERENCES [dbo].[Tags]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PictureTag_Tag'
CREATE INDEX [IX_FK_PictureTag_Tag]
ON [dbo].[PictureTag]
    ([Tags_Id]);
GO

-- Creating foreign key on [PrivateAlbums_Id] in table 'AlbumInvitedUsers'
ALTER TABLE [dbo].[AlbumInvitedUsers]
ADD CONSTRAINT [FK_AlbumInvitedUsers_Album]
    FOREIGN KEY ([PrivateAlbums_Id])
    REFERENCES [dbo].[Albums]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [InvitedUsers_Id] in table 'AlbumInvitedUsers'
ALTER TABLE [dbo].[AlbumInvitedUsers]
ADD CONSTRAINT [FK_AlbumInvitedUsers_User]
    FOREIGN KEY ([InvitedUsers_Id])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AlbumInvitedUsers_User'
CREATE INDEX [IX_FK_AlbumInvitedUsers_User]
ON [dbo].[AlbumInvitedUsers]
    ([InvitedUsers_Id]);
GO

-- Creating foreign key on [FavoriteForUsers_Id] in table 'FavoritePictures'
ALTER TABLE [dbo].[FavoritePictures]
ADD CONSTRAINT [FK_FavoritePictures_User]
    FOREIGN KEY ([FavoriteForUsers_Id])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [FavoritePictures_Id] in table 'FavoritePictures'
ALTER TABLE [dbo].[FavoritePictures]
ADD CONSTRAINT [FK_FavoritePictures_Picture]
    FOREIGN KEY ([FavoritePictures_Id])
    REFERENCES [dbo].[Pictures]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FavoritePictures_Picture'
CREATE INDEX [IX_FK_FavoritePictures_Picture]
ON [dbo].[FavoritePictures]
    ([FavoritePictures_Id]);
GO

-- Creating foreign key on [UserFriends_User1_Id] in table 'UserFriends'
ALTER TABLE [dbo].[UserFriends]
ADD CONSTRAINT [FK_UserFriends_User]
    FOREIGN KEY ([UserFriends_User1_Id])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Friends_Id] in table 'UserFriends'
ALTER TABLE [dbo].[UserFriends]
ADD CONSTRAINT [FK_UserFriends_User1]
    FOREIGN KEY ([Friends_Id])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserFriends_User1'
CREATE INDEX [IX_FK_UserFriends_User1]
ON [dbo].[UserFriends]
    ([Friends_Id]);
GO

-- Creating foreign key on [AlbumId] in table 'Invitations'
ALTER TABLE [dbo].[Invitations]
ADD CONSTRAINT [FK_InvitationAlbum]
    FOREIGN KEY ([AlbumId])
    REFERENCES [dbo].[Albums]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InvitationAlbum'
CREATE INDEX [IX_FK_InvitationAlbum]
ON [dbo].[Invitations]
    ([AlbumId]);
GO

-- Creating foreign key on [AlbumId] in table 'Pictures'
ALTER TABLE [dbo].[Pictures]
ADD CONSTRAINT [FK_AlbumPicture]
    FOREIGN KEY ([AlbumId])
    REFERENCES [dbo].[Albums]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AlbumPicture'
CREATE INDEX [IX_FK_AlbumPicture]
ON [dbo].[Pictures]
    ([AlbumId]);
GO

-- Creating foreign key on [OwnerId] in table 'Albums'
ALTER TABLE [dbo].[Albums]
ADD CONSTRAINT [FK_AlbumOwner]
    FOREIGN KEY ([OwnerId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AlbumOwner'
CREATE INDEX [IX_FK_AlbumOwner]
ON [dbo].[Albums]
    ([OwnerId]);
GO

-- Creating foreign key on [UserId] in table 'UserTags'
ALTER TABLE [dbo].[UserTags]
ADD CONSTRAINT [FK_UserTagUser]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTagUser'
CREATE INDEX [IX_FK_UserTagUser]
ON [dbo].[UserTags]
    ([UserId]);
GO

-- Creating foreign key on [PictureId] in table 'UserTags'
ALTER TABLE [dbo].[UserTags]
ADD CONSTRAINT [FK_UserTagPicture]
    FOREIGN KEY ([PictureId])
    REFERENCES [dbo].[Pictures]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTagPicture'
CREATE INDEX [IX_FK_UserTagPicture]
ON [dbo].[UserTags]
    ([PictureId]);
GO

-- Creating foreign key on [PictureId] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_PictureComment]
    FOREIGN KEY ([PictureId])
    REFERENCES [dbo].[Pictures]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PictureComment'
CREATE INDEX [IX_FK_PictureComment]
ON [dbo].[Comments]
    ([PictureId]);
GO

-- Creating foreign key on [AuthorId] in table 'Comments'
ALTER TABLE [dbo].[Comments]
ADD CONSTRAINT [FK_CommentAuthors]
    FOREIGN KEY ([AuthorId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommentAuthors'
CREATE INDEX [IX_FK_CommentAuthors]
ON [dbo].[Comments]
    ([AuthorId]);
GO

-- Creating foreign key on [TargetId] in table 'FriendRequests'
ALTER TABLE [dbo].[FriendRequests]
ADD CONSTRAINT [FK_FriendRequestTarget]
    FOREIGN KEY ([TargetId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FriendRequestTarget'
CREATE INDEX [IX_FK_FriendRequestTarget]
ON [dbo].[FriendRequests]
    ([TargetId]);
GO

-- Creating foreign key on [InitiatorId] in table 'FriendRequests'
ALTER TABLE [dbo].[FriendRequests]
ADD CONSTRAINT [FK_FriendRequestInitiator]
    FOREIGN KEY ([InitiatorId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FriendRequestInitiator'
CREATE INDEX [IX_FK_FriendRequestInitiator]
ON [dbo].[FriendRequests]
    ([InitiatorId]);
GO

-- Creating foreign key on [UserId] in table 'Invitations'
ALTER TABLE [dbo].[Invitations]
ADD CONSTRAINT [FK_UserInvitation]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInvitation'
CREATE INDEX [IX_FK_UserInvitation]
ON [dbo].[Invitations]
    ([UserId]);
GO

-- Creating foreign key on [AuthorId] in table 'UserTags'
ALTER TABLE [dbo].[UserTags]
ADD CONSTRAINT [FK_UserTagPhotoAlbumUser]
    FOREIGN KEY ([AuthorId])
    REFERENCES [dbo].[PhotoAlbumUsers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTagPhotoAlbumUser'
CREATE INDEX [IX_FK_UserTagPhotoAlbumUser]
ON [dbo].[UserTags]
    ([AuthorId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------